This is the first of a two-part series about how to scale your analyses to larger datasets from your server logs.
For my first post on Search Engine Land, I’ll start by quoting Ian Lurie:
Log file analysis is a lost art. But it can save your SEO butt!
Wise words.
However, getting the data we need out of server log files is usually laborious:
Gigantic log files require robust data ingestion pipelines, a reliable cloud storage infrastructure, and a solid querying system
Meticulous data modeling is also needed in order to convert cryptic, raw logs data into legible bits, suitable for exploratory data analysis and visualization
In the first post of this two-part series, I will show you how to easily scale your analyses to larger datasets, and extract meaningful SEO insights from your server logs.
All of that with just a pinch of Python and a hint of Google Cloud!
Here’s our detailed plan of action:
#1 – I’ll start by giving you a bit of context:
What are log files and why they matter for SEO
How to get hold of them
Why Python alone doesn’t always cut it when it comes to server log analysis
#2 – We’ll then set things up:
Create a Google Cloud Platform account
Create a Google Cloud Storage bucket to store our log files
Use the Command-Line to convert our files to a compliant format for querying
Transfer our files to Google Cloud Storage, manually and programmatically
#3 – Lastly, we’ll get into the nitty-gritty of Pythoning – we will:
Query our log files with Bigquery, inside Colab!
Build a data model that makes our raw logs more legible
Create categorical columns that will enhance our analyses further down the line
Filter and export our results to .csv
In part two of this series (available later this year), we’ll discuss more advanced data modeling techniques in Python to assess:
Bot crawl volume
Crawl budget waste
Duplicate URL crawling
I’ll also show you how to aggregate and join log data to Search Console data, and create interactive visualizations with Plotly Dash!
Excited? Let’s get cracking!
System requirements
We will use Google Colab in this article. No specific requirements or backward compatibility issues here, as Google Colab sits in the cloud.
The log files can be downloaded on Github – 4 sample files of 20 MB each, spanning 4 days (1 day per file)
Be assured that the notebook has been tested with several million rows at lightning speed and without any hurdles!
Preamble: What are log files?
While I don’t want to babble too much about what log files are, why they can be invaluable for SEO, etc. (heck, there are manygreatarticles on the topic already!), here’s a bit of context.
A server log file records every request made to your web server for content.
Every. Single. One.
In their rawest forms, logs are indecipherable, e.g. here are a few raw lines from an Apache webserver:
Daunting, isn’t it?
Raw logs must be “cleansed” in order to be analyzed; that’s where data modeling kicks in. But more on that later.
Whereas the structure of a log file mainly depends on the server (Apache, Nginx, IIS etc…), it has evergreen attributes:
Server IP
Date/Time (also called timestamp)
Method (GET or POST)
URI
HTTP status code
User-agent
Additional attributes can usually be included, such as:
Referrer: the URL that ‘linked’ the user to your site
Redirected URL, when a redirect occurs
Size of the file sent (in bytes)
Time taken: the time it takes for a request to be processed and its response to be sent
Why are log files important for SEO?
If you don’t know why they matter, read this. Time spent wisely!
Accessing your log files
If you’re not sure where to start, the best is to ask your (client’s) Web Developer/DevOps if they can grant you access to raw server logs via FTP, ideally without any filtering applied.
Here are the general guidelines to find and manage log data on the three most popular servers:
Why Pandas alone is not enough when it comes to log analysis
Pandas (an open-source data manipulation tool built with Python) is pretty ubiquitous in data science.
It’s a must to slice and dice tabular data structures, and the mammal works like a charm when the data fits in memory!
That is, a few gigabytes. But not terabytes.
Parallel computing aside (e.g. Dask, PySpark), a database is usually a better solution for big data tasks that do not fit in memory. With a database, we can work with datasets that consume terabytes of disk space. Everything can be queried (via SQL), accessed, and updated in a breeze!
In this post, we’ll query our raw log data programmatically in Python via Google BigQuery. It’s easy to use, affordable and lightning-fast – even on terabytes of data!
The Python/BigQuery combo also allows you to query files stored on Google Cloud Storage. Sweet!
If Google is a nay-nay for you and you wish to try alternatives, Amazon and Microsoft also offer cloud data warehouses. They integrate well with Python too:
GCP is not free, but you can try it for a year with $300 credits, with access to all products. Pretty cool.
Note that once the trial expires, Google Cloud Free Tier will still give you access to most Google Cloud resources, free of charge. With 5 GB of storage per month, it’s usually enough if you want to experiment with small datasets, work on proof of concepts, etc…
Once you have completed sign-up, a new project will be automatically created with a random, and rather exotic, name – e.g. mine was “learned-spider-266010“!
Create our first bucket to store our log files
In Google Cloud Storage, files are stored in “buckets”. They will contain our log files.
To create your first bucket, go to storage > browser > create bucket:
The bucket name has to be unique. I’ve aptly named mine ‘seo_server_logs’!
We then need to choose where and how to store our log data:
#1 Location type – ‘Region’ is usually good enough.
#2 Location – As I’m based in the UK, I’ve selected ‘Europe-West2’. Select your nearest location
#3 Click on ‘continue’
Default storage class: I’ve had good results with ‘nearline‘. It is cheaper than standard, and the data is retrieved quickly enough:
Access to objects: “Uniform” is fine:
Finally, in the “advanced settings” block, select:
#1 – Google-managed key
#2 – No retention policy
#3 – No need to add a label for now
When you’re done, click “‘create.”
You’ve created your first bucket! Time to upload our log data.
Adding log files to your Cloud Storage bucket
You can upload as many files as you wish, whenever you want to!
The simplest way is to drag and drop your files to Cloud Storage’s Web UI, as shown below:
Yet, if you really wanted to get serious about log analysis, I’d strongly suggest automating the data ingestion process!
Here are a few things you can try:
Cron jobs can be set up between FTP servers and Cloud Storage infrastructures:
You can run Python on your local computer via Jupyter notebook or an IDE, or even in the cloud via Google Colab. We’ll use Google Colab in this article.
Remember, the notebook is here, and the code snippets are pasted below, along with explanations.
Import libraries + GCP authentication
We’ll start by running the cell below:
# Import libraries
importpandasaspd
importnumpyasnp
importsocket
importjson
#Connect Python to your GCP project
fromgoogle.colabimportauth
auth.authenticate_user()
It imports the Python libraries we need and redirects you to an authentication screen.
There you’ll have to choose the Google account linked to your GCP project.
Connect to Google Cloud Storage (GCS) and BigQuery
There’s quite a bit of info to add in order to connect our Python notebook to GCS & BigQuery. Besides, filling in that info manually can be tedious!
Fortunately, Google Colab’s forms make it easy to parameterize our code and save time.
The forms in this notebook have been pre-populated for you. No need to do anything, although I do suggest you amend the code to suit your needs.
Here’s how to create your own form: Go to Insert > add form field > then fill in the details below:
When you change an element in the form, its corresponding values will magically change in the code!
Fill in ‘project ID’ and ‘bucket location’
In our first form, you’ll need to add two variables:
Your GCP PROJECT_ID (mine is ‘learned-spider-266010′)
Your bucket location:
To find it, in GCP go to storage > browser > check location in table
print(“the path to your GCS folder is: “+GCS_Full_Path)
Connect Python to Google Cloud Storage and BigQuery
In the third form, you need to give a name to your BigQuery table – I’ve called mine ‘log_sample’. Note that this temporary table won’t be created in your Bigquery account.
Okay, so now things are getting really exciting, as we can start querying our dataset via SQL *without* leaving our notebook – How cool is that?!
As log data is still in its raw form, querying it is somehow limited. However, we can apply basic SQL filtering that will speed up Pandas operations later on.
I have created 2 SQL queries in this form:
“SQL_1st_Filter” to filter any text
“SQL_Useragent_Filter” to select your User-Agent, via a drop-down
Feel free to check the underlying code and tweak these two queries to your needs.
print(‘The SQL query sent to BigQuery is “‘+SQLFilters+‘”‘)
#log_sample
Converting the list output to a Pandas Dataframe
The output generated by BigQuery is a two-dimensional list (also called ‘list of lists’). We’ll need to convert it to a Pandas Dataframe via this code:
# Note that we convert a list of lists, not just a list.
df=pd.DataFrame.from_records(log_sample)
df.info()
Done! We now have a Dataframe that can be wrangled in Pandas!
Data cleansing time, the Pandas way!
Time to make these cryptic logs a bit more presentable by:
As you can see, our new columns httpCodeClass and SEBotClass have been created:
Spotting ‘spoofed’ search engine bots
We still need to tackle one crucial step for SEO: verify that IP addresses are genuinely from Googlebots.
All credit due to the great Tyler Reardon for this bit! Tyler has created searchtools.io, a clever tool that checks IP addresses and returns ‘fake’ Googlebot ones, based on a reverse DNS lookup.
We’ve simply integrated that script into the notebook – code snippet below:
#Define the function
defreverse_dns(ip_address):
”’
This method returns the true host name for a
given IP address
”’
host_name=socket.gethostbyaddr(ip_address)
reversed_dns=host_name[0]
returnreversed_dns
defforward_dns(reversed_dns):
”’
This method returns the first IP address string
that responds as the given domain name
”’
try:
data=socket.gethostbyname(reversed_dns)
ip=str(data)
returnip
exceptException:
print(‘error’)
returnFalse
defip_match(ip, true_ip):
”’
This method takes an ip address used for a reverse dns lookup
and an ip address returned from a forward dns lookup
and determines if they match.
”’
ifip==true_ip:
ip_match=True
else:
ip_match=False
returnip_match
defconfirm_googlebot(host, ip_match):
”’
This method takes a hostname and the results of the ip_match() method
and determines if an ip address from a log file is truly googlebot
Last but not least, Kudos to Hamlet Batista or JR Oakes – Thanks guys for being so inspirational to the SEO community!
Please reach me out on Twitter if questions, or if you need further assistance. Any feedback (including pull requests! :)) is also greatly appreciated!
Happy Pythoning!
This year’s SMX Advanced will feature a brand-new SEO for Developers track with highly-technical sessions – many in live-coding format – focused on using code libraries and architecture models to develop applications that improve SEO. SMX Advanced will be held June 8-10 in Seattle. Register today.